/**
 * Copyright (c) 2019 Coder_League(码农联盟)
 * All rights reserved.
 *
 * File：MerchantsDaoImpl.java
 * History:
 *         2019年5月24日: Initially created, Liangj.
 */
package club.coderleague.ilsp.dao.impl;

import java.util.Arrays;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import club.coderleague.data.jpa.domain.Page;
import club.coderleague.data.jpa.id.support.SnowflakeV4;
import club.coderleague.data.jpa.repository.support.AbstractDataRepositoryExtension;
import club.coderleague.ilsp.common.domain.beans.MerchantRemind;
import club.coderleague.ilsp.common.domain.beans.MerchantsPageBean;
import club.coderleague.ilsp.common.domain.beans.MerchantsSearchName;
import club.coderleague.ilsp.common.domain.beans.MerchantsSearchPhone;
import club.coderleague.ilsp.common.domain.beans.UserSession;
import club.coderleague.ilsp.common.domain.beans.mobile.MerchantsFilterConditions;
import club.coderleague.ilsp.common.domain.beans.mobile.MoblieMerchantInfo;
import club.coderleague.ilsp.common.domain.enums.EntityState;
import club.coderleague.ilsp.common.domain.enums.UserGroup;
import club.coderleague.ilsp.common.util.CommonUtil;
import club.coderleague.ilsp.dao.extension.MerchantsDaoExtension;
import club.coderleague.ilsp.entities.Merchants;

/**
 * 商户
 * @author Liangjing
 */
public class MerchantsDaoImpl extends AbstractDataRepositoryExtension<Merchants, Long, SnowflakeV4> implements MerchantsDaoExtension {

	@Override
	public Page<MerchantsPageBean> getTheAllMerchants(Integer pageIndex, Integer pageSize, Long searchnameentityid, String searchphonehash, boolean isrecycle) {
		Map<String,Object> map = new HashMap<String, Object>();
		StringBuffer qsql = new StringBuffer(" select ms.EntityId as entityid,ms.MerchantName as merchantname,ms.MerchantPhone as merchantphone,ms.IdNumber as idnumber,ms.BcNumber as bcnumber,ms.EntityState as entitystate from Merchants ms where 1=1 ");
		StringBuffer csql = new StringBuffer(" select count(ms.EntityId) from Merchants ms where 1=1 ");
		if(!isrecycle) {
			qsql.append(" and ms.EntityState IN (:istate) ");
			csql.append(" and ms.EntityState IN (:istate) ");
			map.put("istate", Arrays.asList(new Integer[] {EntityState.VALID.getValue(),EntityState.FROZEN.getValue()}));
		}else {
			qsql.append(" and ms.EntityState = :istate ");
			csql.append(" and ms.EntityState = :istate ");
			map.put("istate", EntityState.INVALID.getValue());
		}
		if(!CommonUtil.isEmpty(searchnameentityid) && searchnameentityid != -1) {
			qsql.append(" and ms.entityid = :searchnameentityid ");
			csql.append(" and ms.entityid = :searchnameentityid ");
			map.put("searchnameentityid", searchnameentityid);
		}
		if(!CommonUtil.isEmpty(searchphonehash) && !"-1".equals(searchphonehash)) {
			qsql.append(" and ms.PhoneHash = :searchphonehash ");
			csql.append(" and ms.PhoneHash = :searchphonehash ");
			map.put("searchphonehash", searchphonehash);
		}
		return super.queryCustomBeanPageBySql(MerchantsPageBean.class, csql.toString(), qsql.toString(), pageIndex, pageSize, map);
	}

	@Override
	public Merchants findByMerchantnameAndEntityState(String merchantname, List<Integer> istate) {
		Map<String,Object> map = new HashMap<String, Object>();
		String hql = " from Merchants where MerchantName = :merchantname and EntityState IN (:istate) ";
		map.put("merchantname", merchantname);
		map.put("istate", istate);
		return super.queryEntity(hql, map);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.MerchantsDaoExtension#queryUserSession(java.lang.String)
	 */
	@Override
	public UserSession queryUserSession(String openId) {
		String sql = "select m.entityid as userid, m.merchantname as username from merchants m where m.entitystate = ?0 and m.merchantweixin = ?1";
		
		UserSession us = super.queryCustomBeanBySql(UserSession.class, sql, EntityState.VALID.getValue(), openId);
		if (us != null) us.setUsergroup(UserGroup.MERCHANT.getValue());
		
		return us;
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.MerchantsDaoExtension#updateLoginTime(long)
	 */
	@Override
	public void updateLoginTime(long merchant) {
		String sql = "update merchants set lastlogin = ?0 where entityid = ?1";
		super.executeUpdateBySql(sql, new Date(), merchant);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.MerchantsDaoExtension#queryBoundInfo(java.lang.String)
	 */
	@Override
	public Map<String, Object> queryBoundInfo(String openId) {
		String jpql = "select entityid as boundid, entitystate as boundstate, phonehash as boundphone from Merchants where merchantweixin = ?0";
		return super.queryMap(jpql, openId);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.MerchantsDaoExtension#queryExistsMerchant(java.lang.String)
	 */
	@Override
	public Merchants queryExistsMerchant(String phoneHash) {
		String jpql = "from Merchants where entitystate = ?0 and phonehash = ?1";
		return super.queryEntity(jpql, EntityState.VALID.getValue(), phoneHash);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.MerchantsDaoExtension#clearBound(java.lang.String)
	 */
	@Override
	public void clearBound(String boundId) {
		String sql = "update merchants set merchantweixin = null where entityid = ?0";
		super.executeUpdateBySql(sql, boundId);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.MerchantsDaoExtension#queryMerchantRemind(long)
	 */
	@Override
	public MerchantRemind queryMerchantRemind(long merchant) {
		String sql = "select entityid as merchantid, merchantphone, smnotify from merchants where entitystate = ?0 and entityid = ?1";
		return super.queryCustomBeanBySql(MerchantRemind.class, sql, EntityState.VALID.getValue(), merchant);
	}

	@Override
	public List<Map<String, Object>> getTheAllMarkets() {
		String sql = " select CONCAT(entityid) as id,MarketName as name from Markets where EntityState = ?0 ";
		return super.queryMapListBySql(sql, EntityState.VALID.getValue());
	}

	@Override
	public boolean getIsHaveTheSettleStall(Long marketid, String settlestall,Long entityid) {
		if(!CommonUtil.isEmpty(entityid)) {
			String sql = " select count(EntityId) from MerchantSettles where MarketId = ?0 and SettleStall = ?1 and EntityState = ?2 and EntityId != ?3 ";
			Long i = super.queryLongBySql(sql, marketid, settlestall, EntityState.VALID.getValue(), entityid);
			return (i != null && i > 0) ? true : false;
		}else {
			String sql = " select count(EntityId) from MerchantSettles where MarketId = ?0 and SettleStall = ?1 and EntityState = ?2 ";
			Long i = super.queryLongBySql(sql, marketid, settlestall, EntityState.VALID.getValue());
			return (i != null && i > 0) ? true : false;
		}
	}

	@Override
	public Long getTheMaxAddOneSettleSerials(Long marketid) {
		String sql = " select SettleSerials from MerchantSettles where MarketId = ?0 order by SettleSerials desc limit 1 ";
		Long i = super.queryLongBySql(sql, marketid);
		return (i != null && i > 0) ? i + 1 : 1001;
	}

	@Override
	public List<MerchantsFilterConditions> getTheAllMerchantFilterConditions(Long goodgroupid, String key) {
		Map<String,Object> map = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer(" select concat(m.EntityId) as entityid, m.MerchantName as name from Goods g,Merchants m,GoodSales gsale,GoodSpecs gspecs where g.EntityState = :istate and g.MerchantId = m.entityid and m.EntityState = :istate and gsale.SpecId = gspecs.EntityId and gsale.EntityState = :published and gspecs.GoodId = g.EntityId and gspecs.EntityState = :istate ");
		if(!CommonUtil.isEmpty(goodgroupid)) {
			sql.append(" and g.GroupId = :goodgroupid ");
			map.put("goodgroupid", goodgroupid);
		}
		if(!CommonUtil.isEmpty(key)) {
			sql.append(" and g.GoodName like :key ");
			map.put("key", "%"+key+"%");
		}
		sql.append(" group by m.EntityId, m.MerchantName ");
		map.put("istate", EntityState.VALID.getValue());
		map.put("published", EntityState.PUBLISHED.getValue());
		return super.queryCustomBeanListBySql(MerchantsFilterConditions.class, sql.toString(), map);
	}

	@Override
	public List<MerchantsSearchName> getTheAllSearchMerchant() {
		String sql = " select EntityId as entityid,MerchantName as merchantname from Merchants where EntityState = ?0 ";
		return super.queryCustomBeanListBySql(MerchantsSearchName.class, sql, EntityState.VALID.getValue());
	}

	@Override
	public List<MerchantsSearchPhone> getTheAllSearchMerchantPhoneHash() {
		String sql = " select EntityId as entityid,MerchantPhone as merchantphone,PhoneHash as phonehash from Merchants where EntityState = ?0 ";
		return super.queryCustomBeanListBySql(MerchantsSearchPhone.class, sql, EntityState.VALID.getValue());
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.MerchantsDaoExtension#getMerchanstListDao(java.util.Map)
	 */
	@Override
	public Page<Merchants> getMerchanstListDao(Map<String, Object> params) {
		Map<String, Object> param = new HashMap<String, Object>();
		String qsql = "from Merchants where entitystate = :entitystate and autosettle = 1 and totalamount > 0 ";
		String csql = "select count(entityid) from Merchants where entitystate = :entitystate and autosettle = 1 and totalamount > 0 ";
		param.put("entitystate", EntityState.VALID.getValue());
		if (params.get("keyword") != null && StringUtils.isNotBlank(params.get("keyword").toString())) {
			qsql += "and entityid = :entityid ";
			csql += "and entityid = :entityid ";
            param.put("entityid", Long.valueOf(params.get("keyword").toString()));
        }
		qsql += "order by ModifyTime ";
		return super.queryEntityPage(csql, qsql, Integer.parseInt(params.get("pageIndex").toString()), Integer.parseInt(params.get("pageSize").toString()), param);
	}

	@Override
	public List<Map<String, Object>> getTheAllParentBanks() {
		String sql = " select EntityId as entityid,BankName as bankname from Banks where ParentBank is null and EntityState = ?0 ";
		return super.queryMapListBySql(sql, EntityState.VALID.getValue());
	}

	@Override
	public List<Map<String, Object>> getTheAllSonBanks(Long entityid) {
		String sql = " select EntityId as entityid,BankName as bankname from Banks where ParentBank = ?0 and EntityState = ?1 ";
		return super.queryMapListBySql(sql, entityid, EntityState.VALID.getValue());
	}
	
	@Override
	public Map<String, Object> getTheBankNameAndParentName(Long merchantid) {
		String sql = " select b.BankName as bankname,(select bs.BankName from Banks bs where bs.entityid = b.ParentBank) as parentbankname from Merchants m, Banks b where b.entityid = m.bankid and m.entityid = ?0 ";
		return super.queryMapBySql(sql, merchantid);
	}

	@Override
	public MoblieMerchantInfo getTheMerchantInfo(Long merchantid) {
		Map<String,Object> map = new HashMap<String, Object>();
		String sql = " select m.entityid,m.merchantname,m.merchantphone,m.idnumber "
				+ " ,(select bs.BankName from Banks bs where bs.entityid = b.ParentBank ) as bankkfname "
				+ " ,b.BankName as bankwdname,m.accountname,m.bcnumber "
				+ " ,m.idpositive,m.idnegative,m.businesslicense "
				+ " ,(m.TotalAmount - m.LockedAmount) as surplusmoeny "
				+ " ,(m.TotalAmount - m.LockedAmount - ifnull((select SUM(cr.ChangedAmount) from Orders o,CapitalRecords cr where o.MemberId = m.entityid and o.entityid = cr.OrderId and o.EntityState = :otsstate and TIMESTAMPDIFF(SECOND,o.RefundEndTime,now()) >= 0),0)) as realsurplusmoeny "
				+ " , m.smnotify, m.autosettle "
				+ " from Merchants m, Banks b where m.BankId = b.entityid and m.entityid = :merchantid ";
		map.put("otsstate", EntityState.OTS.getValue());
		map.put("merchantid", merchantid);
		return super.queryCustomBeanBySql(MoblieMerchantInfo.class, sql, map);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.MerchantsDaoExtension#findByMarketid(java.lang.Long)
	 */
	@Override
	public List<Map<String, Object>> findByMarketid(Long marketid) {
		String sql = "select ms.entityid as entityid, m.entityid as merchantid, m.accountname as accountname, ms.settlestall as settlestall"
				+ " from merchants m"
				+ " inner join merchantsettles ms on ms.entitystate = ?1 and ms.marketid = ?0 and ms.merchantid = m.entityid"
				+ " where m.entitystate = ?1";
		return super.queryMapListBySql(sql, marketid, EntityState.VALID.getValue());
	}
}
